﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Search_AdvancedSearch : System.Web.UI.Page
{  
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            //Replace the string "PeDALS_PRODConnectionsString1" in the line below with the correct SQL connection string from your web.config file
            SqlConnection scConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["PeDALS_PRODConnectionString1"].ConnectionString);
            SqlCommand scCommand = new SqlCommand("SELECT s.SeriesId, p.ProvenanceName + ' / ' + s.SeriesTitle AS SeriesText FROM Provenance AS p INNER JOIN Series AS s ON p.ProvenanceId = s.ProvenanceId ORDER BY SeriesText", scConnect);
            scConnect.Open();
            SqlDataReader sdrReader = scCommand.ExecuteReader();
            DropDownProvenances.DataSource = sdrReader;
            DropDownProvenances.DataBind();
            ListItem liItem = new ListItem("- ALL -", "0");
            DropDownProvenances.Items.Insert(0, liItem);
            scConnect.Close();
            sdrReader = null;
            scCommand = null;
            scConnect = null;

        }
    }

    protected void btnAdvSearch_Click(object sender, EventArgs e)
    {
        //Replace the string "PeDALS_PRODConnectionsString1" in the line below with the correct SQL connection string from your web.config file
        SqlConnection scConnect = new SqlConnection(ConfigurationManager.ConnectionStrings["PeDALS_PRODConnectionString1"].ConnectionString);
        SqlDataAdapter sdaAdapter = new SqlDataAdapter("PPC_AdvancedSearch", scConnect);
        sdaAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

        if (int.Parse(DropDownProvenances.SelectedValue) > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@SeriesID", SqlDbType.Int, 8);
            sdaAdapter.SelectCommand.Parameters["@SeriesID"].Value = int.Parse(DropDownProvenances.SelectedValue);
        }

        if (ItemTitle0.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@ItemTitle0", SqlDbType.VarChar, 250);
            sdaAdapter.SelectCommand.Parameters["@ItemTitle0"].Value = ItemTitle0.Text;
        }

        if (ItemTitle1.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@ItemTitle1", SqlDbType.VarChar, 250);
            sdaAdapter.SelectCommand.Parameters["@ItemTitle1"].Value = ItemTitle1.Text;
            if (ItemTitle0.Text.Length > 0)
            {
                sdaAdapter.SelectCommand.Parameters.Add("@ItemTitleConnector", SqlDbType.VarChar, 3);
                sdaAdapter.SelectCommand.Parameters["@ItemTitleConnector"].Value = ItemTitleConnector.Text;
            }
        }

        if (ItemParty0.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@ItemParty0", SqlDbType.VarChar, 800);
            sdaAdapter.SelectCommand.Parameters["@ItemParty0"].Value = ItemParty0.Text;
        }

        if (ItemParty1.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@ItemParty1", SqlDbType.VarChar, 800);
            sdaAdapter.SelectCommand.Parameters["@ItemParty1"].Value = ItemParty1.Text;
            if (ItemParty0.Text.Length > 0)
            {
                sdaAdapter.SelectCommand.Parameters.Add("@ItemPartyConnector", SqlDbType.VarChar, 3);
                sdaAdapter.SelectCommand.Parameters["@ItemPartyConnector"].Value = ItemPartyConnector.Text;
            }
        }

        if (GeographicLocation0.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@GeographicLocation0", SqlDbType.VarChar, 250);
            sdaAdapter.SelectCommand.Parameters["@GeographicLocation0"].Value = GeographicLocation0.Text;
        }

        if (GeographicLocation1.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@GeographicLocation1", SqlDbType.VarChar, 250);
            sdaAdapter.SelectCommand.Parameters["@GeographicLocation1"].Value = GeographicLocation1.Text;
            if (GeographicLocation0.Text.Length > 0)
            {
                sdaAdapter.SelectCommand.Parameters.Add("@GeographicLocationConnector", SqlDbType.VarChar, 3);
                sdaAdapter.SelectCommand.Parameters["@GeographicLocationConnector"].Value = GeographicLocationConnector.Text;
            }
        }

        if (SeriesSubject0.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@SeriesSubject0", SqlDbType.VarChar, 100);
            sdaAdapter.SelectCommand.Parameters["@SeriesSubject0"].Value = SeriesSubject0.Text;
        }

        if (SeriesSubject1.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@SeriesSubject1", SqlDbType.VarChar, 100);
            sdaAdapter.SelectCommand.Parameters["@SeriesSubject1"].Value = SeriesSubject1.Text;
            if (SeriesSubject0.Text.Length > 0)
            {
                sdaAdapter.SelectCommand.Parameters.Add("@SeriesSubjectConnector", SqlDbType.VarChar, 3);
                sdaAdapter.SelectCommand.Parameters["@SeriesSubjectConnector"].Value = SeriesSubjectConnector.Text;
            }
        }

        if (SeriesKeyword0.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@SeriesKeyword0", SqlDbType.VarChar, 50);
            sdaAdapter.SelectCommand.Parameters["@SeriesKeyword0"].Value = SeriesKeyword0.Text;
        }

        if (SeriesKeyword1.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@SeriesKeyword1", SqlDbType.VarChar, 50);
            sdaAdapter.SelectCommand.Parameters["@SeriesKeyword1"].Value = SeriesKeyword1.Text;
            if (SeriesKeyword0.Text.Length > 0)
            {
                sdaAdapter.SelectCommand.Parameters.Add("@SeriesKeywordConnector", SqlDbType.VarChar, 3);
                sdaAdapter.SelectCommand.Parameters["@SeriesKeywordConnector"].Value = SeriesKeywordConnector.Text;
            }
        }

        if (DateRange0.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@DateRange0", SqlDbType.DateTime);
            sdaAdapter.SelectCommand.Parameters["@DateRange0"].Value = DateRange0.Text;
        }

        if (DateRange1.Text.Length > 0)
        {
            sdaAdapter.SelectCommand.Parameters.Add("@DateRange1", SqlDbType.DateTime);
            sdaAdapter.SelectCommand.Parameters["@DateRange1"].Value = DateRange1.Text;
        }
        scConnect.Open();
        DataSet dsData = new DataSet();
        sdaAdapter.Fill(dsData);
        Cache["provResults"] = dsData.Tables[0];
        provListView.DataSource = (DataTable) Cache["provResults"];
        provListView.DataBind();
        Cache["seriesResults"] = dsData.Tables[1];
        seriesListView.DataSource = (DataTable)Cache["seriesResults"];
        seriesListView.DataBind();
        Cache["itmResults"] = dsData.Tables[2];
        itmListView.DataSource = (DataTable) Cache["itmResults"];
        itmListView.DataBind();
        //Cache["results"] = dsData;
        scConnect.Close();
        sdaAdapter = null;
        scConnect = null;
    }
    protected void seriesList_PreRender(object sender, EventArgs e)
    {
        provListView.DataSource = (DataTable)Cache["provResults"];
        provListView.DataBind(); 
        seriesListView.DataSource = (DataTable)Cache["seriesResults"];
        seriesListView.DataBind();
        base.OnPreRender(e);
    }
    protected void itmList_PreRender(object sender, EventArgs e)
    {
        itmListView.DataSource = (DataTable)Cache["itmResults"];
        itmListView.DataBind();
        base.OnPreRender(e);
    }
}